﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace BO
{
    /// <summary>
    /// Summary description for VYC24BO
    /// </summary>
    public class VYC24BO:DBConnectionBO
    {
        public VYC24BO():base()
        {
            
        }
        public List<ModelVYC24> GetAllList()
        {
            var list = new List<ModelVYC24>();
            const string query = "SELECT [Id],[IdCountry],[UniqueId],[Ten],[TomTat],[AnhDaiDien],[NoiDung],[LuotXem],[Address],[TungDo],[ViDo],[NgayCapNhat],[TrangThai] FROM [VYC-24]";
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var model = new ModelVYC24();

                model.Id = (dataTable.Rows[i][0] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][0]);
                model.IdCountry = (dataTable.Rows[i][1] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][1]);                
                model.UniqueId = (dataTable.Rows[i][2] == DBNull.Value) ? string.Empty : dataTable.Rows[i][2].ToString();
                model.Ten = (dataTable.Rows[i][3] == DBNull.Value) ? string.Empty : dataTable.Rows[i][3].ToString();
                model.TomTat = (dataTable.Rows[i][4] == DBNull.Value) ? string.Empty : dataTable.Rows[i][4].ToString();
                model.AnhDaiDien = (dataTable.Rows[i][5] == DBNull.Value) ? string.Empty : dataTable.Rows[i][5].ToString();
                model.NoiDung = (dataTable.Rows[i][6] == DBNull.Value) ? string.Empty : dataTable.Rows[i][6].ToString();
                model.LuotXem = (dataTable.Rows[i][7] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][7]);
                model.Address = (dataTable.Rows[i][8] == DBNull.Value) ? string.Empty : dataTable.Rows[i][8].ToString();
                model.TungDo = (dataTable.Rows[i][9] == DBNull.Value) ? string.Empty : dataTable.Rows[i][9].ToString();
                model.ViDo = (dataTable.Rows[i][10] == DBNull.Value) ? string.Empty : dataTable.Rows[i][10].ToString();
                model.NgayCapNhat = (dataTable.Rows[i][11] == DBNull.Value) ? DateTime.Now : (DateTime)dataTable.Rows[i][11];
                model.TrangThai = (dataTable.Rows[i][12] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][12]);

                list.Add(model);
            }
            return list;
        }

        /// <summary>
        /// Add new a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddData(ModelVYC24 model)
        {
            var query = "INSERT INTO [VYC-24]([IdCountry],[UniqueId],[Ten],[TomTat],[AnhDaiDien],[NoiDung],[LuotXem],[Address],[TungDo],[ViDo],[NgayCapNhat],[TrangThai]) VALUES (@idcountry, @uniqueid, @ten, @tomtat,@anhdaidien,@noidung,@luotxem, @address,@tungdo,@vido,GETDATE(),@trangthai)";
            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@idcountry", SqlDbType.Int).Value = model.IdCountry;
            sqlCommand.Parameters.Add("@uniqueid", SqlDbType.VarChar).Value = model.UniqueId;
            sqlCommand.Parameters.Add("@ten", SqlDbType.NVarChar).Value = model.Ten;
            sqlCommand.Parameters.Add("@tomtat", SqlDbType.NVarChar).Value = model.TomTat;
            sqlCommand.Parameters.Add("@anhdaidien", SqlDbType.VarChar).Value = model.AnhDaiDien;
            sqlCommand.Parameters.Add("@noidung", SqlDbType.NText).Value = model.NoiDung;
            sqlCommand.Parameters.Add("@luotxem", SqlDbType.Int).Value = model.LuotXem;
            sqlCommand.Parameters.Add("@address", SqlDbType.NVarChar).Value = model.Address;
            sqlCommand.Parameters.Add("@tungdo", SqlDbType.VarChar).Value = model.TungDo;
            sqlCommand.Parameters.Add("@vido", SqlDbType.VarChar).Value = model.ViDo;
            sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = model.TrangThai;
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;                
            }
        }

        /// <summary>
        /// Edit a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int EditData(ModelVYC24 model)
        {
            var query = "UPDATE [VYC-24] SET [IdCountry] = @idcountry, [UniqueId] = @uniqueid, [Ten] = @ten, [TomTat] = @tomtat,[AnhDaiDien] = @anhdaidien,[NoiDung] = @noidung,[LuotXem] = @luotxem,[Address] = @address,[TungDo] = @tungdo,[ViDo] = @vido,[NgayCapNhat] = GETDATE(), [TrangThai] = @trangthai WHERE Id = @id";
            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@idcountry", SqlDbType.Int).Value = model.IdCountry;
            sqlCommand.Parameters.Add("@uniqueid", SqlDbType.VarChar).Value = model.UniqueId;
            sqlCommand.Parameters.Add("@ten", SqlDbType.NVarChar).Value = model.Ten;
            sqlCommand.Parameters.Add("@tomtat", SqlDbType.NVarChar).Value = model.TomTat;
            sqlCommand.Parameters.Add("@anhdaidien", SqlDbType.VarChar).Value = model.AnhDaiDien;
            sqlCommand.Parameters.Add("@noidung", SqlDbType.NText).Value = model.NoiDung;
            sqlCommand.Parameters.Add("@luotxem", SqlDbType.Int).Value = model.LuotXem;
            sqlCommand.Parameters.Add("@address", SqlDbType.NVarChar).Value = model.Address;
            sqlCommand.Parameters.Add("@tungdo", SqlDbType.VarChar).Value = model.TungDo;
            sqlCommand.Parameters.Add("@vido", SqlDbType.VarChar).Value = model.ViDo;
            sqlCommand.Parameters.Add("@trangthai", SqlDbType.Bit).Value = model.TrangThai;
            sqlCommand.Parameters.Add("@id", SqlDbType.Int).Value = model.Id;
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Delete a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int DeleteData(ModelVYC24 model)
        {
            var query = "DELETE FROM [VYC-24] " +
                        " WHERE Id = " + model.Id;
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Get by id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<ModelVYC24> GetById(int id)
        {
            var list = new List<ModelVYC24>();
            var query = "SELECT [Id],[IdCountry],[UniqueId],[Ten],[TomTat],[AnhDaiDien],[NoiDung],[LuotXem],[Address],[TungDo],[ViDo],[NgayCapNhat],[TrangThai] FROM [VYC-24] WHERE Id = "+ id; 
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var model = new ModelVYC24();

                model.Id = (dataTable.Rows[i][0] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][0]);
                model.IdCountry = (dataTable.Rows[i][1] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][1]);
                model.UniqueId = (dataTable.Rows[i][2] == DBNull.Value) ? string.Empty : dataTable.Rows[i][2].ToString();
                model.Ten = (dataTable.Rows[i][3] == DBNull.Value) ? string.Empty : dataTable.Rows[i][3].ToString();
                model.TomTat = (dataTable.Rows[i][4] == DBNull.Value) ? string.Empty : dataTable.Rows[i][4].ToString();
                model.AnhDaiDien = (dataTable.Rows[i][5] == DBNull.Value) ? string.Empty : dataTable.Rows[i][5].ToString();
                model.NoiDung = (dataTable.Rows[i][6] == DBNull.Value) ? string.Empty : dataTable.Rows[i][6].ToString();
                model.LuotXem = (dataTable.Rows[i][7] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][7]);
                model.Address = (dataTable.Rows[i][8] == DBNull.Value) ? string.Empty : dataTable.Rows[i][8].ToString();
                model.TungDo = (dataTable.Rows[i][9] == DBNull.Value) ? string.Empty : dataTable.Rows[i][9].ToString();
                model.ViDo = (dataTable.Rows[i][10] == DBNull.Value) ? string.Empty : dataTable.Rows[i][10].ToString();
                model.NgayCapNhat = (dataTable.Rows[i][11] == DBNull.Value) ? DateTime.Now : (DateTime)dataTable.Rows[i][11];
                model.TrangThai = (dataTable.Rows[i][12] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][12]);

                list.Add(model);
            }
            return list;
        }
    }
}